﻿using MySqlConnector;
using Org.BouncyCastle.Crmf;
using S7Simulate.Utils;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using sz.db.model;

namespace S7Simulate
{
    public partial class ManualSort : Form
    {
        public ManualSort()
        {
            InitializeComponent();
        }
        public class DataTableToEntityConverter
        {
            private Dictionary<string, string> columnToPropertyMapping;

            public DataTableToEntityConverter(Dictionary<string, string> columnToPropertyMapping)
            {
                this.columnToPropertyMapping = columnToPropertyMapping;
            }

            public List<T> ConvertDataTable<T>(DataTable dataTable) where T : new()
            {
                List<T> entities = new List<T>();

                foreach (DataRow row in dataTable.Rows)
                {
                    T entity = new T();
                    MapDataRowToEntity(row, entity);
                    entities.Add(entity);
                }

                return entities;
            }

            private void MapDataRowToEntity<T>(DataRow row, T entity) where T : new()
            {
                foreach (DataColumn column in row.Table.Columns)
                {
                    string columnName = column.ColumnName;
                    if (columnToPropertyMapping.ContainsKey(columnName))
                    {
                        string propertyName = columnToPropertyMapping[columnName];
                        var property = typeof(T).GetProperty(propertyName);
                        if (property != null && row[column] != DBNull.Value)
                        {
                            property.SetValue(entity, Convert.ChangeType(row[column], property.PropertyType));
                        }
                    }
                }
            }
        }
        // 列名到属性名的映射
        Dictionary<string, string> columnToPropertyMapping = new Dictionary<string, string>
        {
            { "序号", "SerialNo" },
            { "批次号", "BatchName" },
            { "订货商", "Indentor" },
            { "图号", "PicNo" },
            { "客户", "Customer" },
            { "地址", "Address" },
            { "子单号", "OrderSon" },
            { "订单号", "Order" },
            { "条码", "BarCode" },
            { "板件名", "PlateName" },
            { "包装条码", "PackCode" },
            { "包裹号", "PackNo" },
            { "包裹长", "PackLength" },
            { "包裹宽", "PackWidth" },
            { "包裹高", "PackHeight" },
            { "包装重", "PackWeight" },
            { "板件长", "Length" },
            { "板件宽", "Width" },
            { "数量", "Qty" },
            { "层高", "LayerHeight" },
            { "X坐标", "XPo" },
            { "Y坐标", "YPo" },
            { "Z坐标", "ZPo" },
            { "类型", "Type" },
            { "生产线", "ProductLine" }
        };

        decimal rowsCount = 0;
        decimal index = 0;
        object values = 0;

        List<string> SQLStringList = new List<string>();
        /// <summary>
        /// bus_wood_task、bus_wood_information表的插入语句
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="obj"></param>
        private void CreateSQLString(string tablename, params object[] obj)
        {
            var sql_task = "replace INTO bus_wood_task (barcode,processing_flag,status,program_m1,program_m2,feed_speed,last_updated_time,import_mode,import_by,created_time,send_to_cloud_flag,grooving_side1,grooving_side2 ) VALUES (";

            var sql_info = "replace INTO bus_wood_information (id,workshop_id,barcode,description_zh,batch_id,order_number,production_date,material_code,quantity,raw_length,raw_width,finish_length,finish_width,finish_thickness,office_id,line_code,import_mode,import_by,dvm,project_name,barcode1,barcode2) values(";

            string sql = tablename.Contains("task") ? sql_task : sql_info;
            try
            {
                foreach (var ob in obj)
                {
                    if (ob != null && ob.ToString().ToUpper() != "NULL")
                    {
                        sql += "'" + ob + "',";
                    }
                    else
                    {
                        sql += "NULL,";
                    }
                }
                sql += ");";
                sql = sql.Replace(",)", ")");

                SQLStringList.Add(sql);
            }
            catch
            {

            }
        }
        /// <summary>
        /// bus_package_list、bus_packing_information表的插入语句
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="obj"></param>
        private void CreateSQLString2(string tablename, params object[] obj)
        {
            var sql_list = "replace  into bus_package_list(batch_id,package_id,barcode,status,sort,layer,x,y,z,last_updated_time,import_mode,import_by,created_time,send_to_cloud_flag,is_revolve,length,width,thickness,material_code,description_zh )  values ( ";
            var sql_info = "replace  into bus_packing_information(id,workshop_id,batch_id,order_number,package_id,quantity,description_zh,office_id,import_mode,import_by,dvm,layers,carton_length,carton_width,carton_height,weight )  values ( ";
            string sql = tablename.Contains("list") ? sql_list : sql_info;
            try
            {
                foreach (var ob in obj)
                {
                    if (ob != null && ob.ToString().ToUpper() != "NULL")
                    {
                        sql += "'" + ob + "',";
                    }
                    else
                    {
                        sql += "NULL,";
                    }
                }
                sql += ");";
                sql = sql.Replace(",)", ")");

                SQLStringList.Add(sql);
            }
            catch
            {

            }
        }
        /// <summary> 
        /// 字符串中多个连续空格转为一个空格 
        /// </summary> 
        /// <param name="str">待处理的字符串</param> 
        /// <returns>合并空格后的字符串</returns> 
        public static string MergeSpace(string str)
        {
            if (str != string.Empty && str != null && str.Length > 0)
            {
                str = new System.Text.RegularExpressions.Regex("[\\s]+").Replace(str, " ");
            }
            return str;
        }
        bool isSuspend = false;
        bool isRun = false;
        /// <summary>
        /// 包裹信息model
        /// </summary>
        public class Baoguo
        {
            private string _barcode;
            private string _orderid;
            private decimal _len;
            private decimal _width;
            private decimal _height;
            private int _count;
            private int _xiangid;
            private int _sortid;
            private int _layer;
            private string _packetid;
            private string _barcode1;
            private string _barcode2;
            private string _material_code;
            private string _description_zh;

            public string description_zh
            {
                set { _description_zh = value; }
                get { return _description_zh; }
            }

            public string material_code
            {
                set { _material_code = value; }
                get { return _material_code; }
            }

            public string barcode1
            {
                set { _barcode1 = value; }
                get { return _barcode1; }
            }

            public string barcode2
            {
                set { _barcode2 = value; }
                get { return _barcode2; }
            }

            public string packetid
            {
                set { _packetid = value; }
                get { return _packetid; }
            }

            public int layer
            {
                set { _layer = value; }
                get { return _layer; }
            }
            public string barcode
            {
                set { _barcode = value; }
                get { return _barcode; }
            }
            public string orderid
            {
                set { _orderid = value; }
                get { return _orderid; }
            }
            public decimal len
            {
                set { _len = value; }
                get { return _len; }
            }
            public decimal width
            {
                set { _width = value; }
                get { return _width; }
            }
            public decimal height
            {
                set { _height = value; }
                get { return _height; }
            }
            public int count
            {
                set { _count = value; }
                get { return _count; }
            }
            public int xiangid
            {
                set { _xiangid = value; }
                get { return _xiangid; }
            }
            public int sortid
            {
                set { _sortid = value; }
                get { return _sortid; }
            }
        }
        private void btnGoA_Click(object sender, EventArgs e)
        {
            
            int sNo = 1;

            try
            {
                //批次名
                var txtBatch = txtBatchName.Text.Trim();
                SqlSugarClient scSqlServerDb = SqlSugarUtil.GetSqlServerSqlClientl();
                SqlSugarClient localMysql = SqlSugarUtil.GetLocalMySqlClient();
                SqlSugarClient midMysql = SqlSugarUtil.GetMidMysqlClient();
                SqlSugarClient midSqlServerDb = SqlSugarUtil.GetMidSqlServerSqlClientl();
                //DataTable转换为实体对象
                DataTableToEntityConverter converter = new DataTableToEntityConverter(columnToPropertyMapping);
                //商川中间数据库连接,获取ProductTask表ProductDate最近30天的批次数据
                    //log
                    Log4NetHelper.Debug($"开始获取批次数据{txtBatch}");
                    var batchName = txtBatch;
                    //通过存储过程调用商川数据
                    var parameters = new SugarParameter[] { new SugarParameter("@slnname", batchName) };

                    var dataTable = scSqlServerDb.Ado.UseStoredProcedure().GetDataTable("SZyubaozhuang", parameters);
                    //出现查询数量为1不添加 考虑事务
                    if (dataTable.Rows.Count > 1)
                    {
                    Log4NetHelper.Debug($"获取批次:{batchName}数据成功,数量{dataTable.Rows.Count}");
                        try
                        {
                            //先删除中间库此批次再添加
                            midMysql.Deleteable<ScSortDatas>().Where(it => it.BatchName == batchName).ExecuteCommand();

                            List<ScSortDatas> sortList = converter.ConvertDataTable<ScSortDatas>(dataTable);
                            List<bus_wood_information> wood_infoList = new List<bus_wood_information>();
                            List<bus_wood_task> wood_taskList = new List<bus_wood_task>();
                            List<bus_package_list> bus_Package_List_Models = new List<bus_package_list>();
                            List<bus_packing_information> bus_Packing_Information_Models = new List<bus_packing_information>();
                            //临时包裹存放字典
                            Dictionary<string, int> tempPackageDic = new Dictionary<string, int>();
                            //错误信息字典
                            Dictionary<int, string> errInfoDic = new Dictionary<int, string>();
                            string errstr = null;

                            //遍历批次详情信息
                            List<ScSortDatas> dt = sortList;
                            //dt集合不为空的情况 集合数量赋值给rowsCount
                            rowsCount = dt != null ? dt.Count : 0;
                            index = 0;
                            List<Baoguo> bgList = new List<Baoguo>();
                            ScSortDatas datas = new ScSortDatas();

                            if (rowsCount > 0 && dt[0].BarCode != null)
                            {
                                /*foreach (ScSortDatas row in dt) {
                                    try
                                    {
                                        //插入中间库
                                        midMysql.Insertable(row).ExecuteCommand();
                                    }
                                    catch (Exception ez)
                                    {
                                        Log4NetHelper.Error("InsertMidLoacal---Error:" + ez.Message);
                                        param.WriterLog("InsertMidLoacal---Error:" + ez.Message, '0');
                                    }
                                }
                                //查询中间数据库数据并根据
                                List<ScSortDatas> scSortDatas = midMysql.Queryable<ScSortDatas>().Where(it => it.BatchName == batchName).ToList();
*/
                                //遍历集合添加到中间表和包裹集合
                                foreach (ScSortDatas row in dt)
                                {
                                    //初始化baoguo实体对象
                                    Baoguo bg = new Baoguo();
                                    var _barcode = "";
                                    try
                                    {

                                        _barcode = row.BarCode.TrimStart().TrimEnd();
                                        bg.barcode = _barcode;
                                        bg.count = int.Parse(row.Qty.Trim());
                                        bg.len = decimal.Parse(row.Length);
                                        bg.width = decimal.Parse(row.Width);
                                        bg.height = decimal.Parse("18");
                                        bg.orderid = row.Order;
                                        bg.xiangid = int.Parse(row.PackNo);
                                        bg.packetid = row.PackCode;
                                        bg.barcode2 = batchName;
                                        row.BatchName = batchName;
                                        try
                                        {
                                            int layer = 0;
                                            layer = (int.Parse(row.LayerHeight) / 18) + 1;
                                            bg.layer = layer;
                                        }
                                        catch
                                        {
                                            bg.layer = 0;
                                        }


                                        try
                                        {
                                            bg.sortid = sNo;
                                            sNo++;
                                        }
                                        catch
                                        {
                                            bg.sortid = 0;
                                        }

                                        bgList.Add(bg);

                                    }
                                    catch (Exception ex)
                                    {
                                        Log4NetHelper.Error("BaoguoInsert---Error:" + ex.Message);
                                        //param.WriterLog($"BaoguoInsert---Error:{ex.Message},批次:{batchName}", '0');
                                    }

                                    for (int k = 0; k < 5; k++)
                                    {
                                        try
                                        {
                                            //插入中间库
                                            int re = midMysql.Insertable(row).ExecuteCommand();
                                            if (re > 0)
                                            {
                                                break;
                                            }
                                        }
                                        catch (Exception ez)
                                        {
                                            Log4NetHelper.Error($"batchName{batchName},InsertMidLoacal---Error:" + ez.Message);
                                        }
                                    }

                                }
                            }
                            //得到去重排序后的包裹集合
                            List<Baoguo> newList = new List<Baoguo>();
                            //按照箱号升序排序 按照面积降序排序 
                            var newList1 = bgList.OrderBy(x => x.xiangid).OrderByDescending(x => x.len * x.width).ToList();

                            //var maxXiangid = newList1.Max(x => x.xiangid);
                            //映像新集合 存放包裹id  packetid集合 去重包裹id用
                            var pNewList1 = newList1.Select(x => x.packetid).ToList<string>();
                            //去重
                            var pNewList = pNewList1.Distinct().ToList();
                            var maxXiangid = pNewList.Count;

                            try
                            {
                                for (int j = 0; j < maxXiangid; j++)
                                {
                                    //排序后的集合查找去重后包裹集合  
                                    var newlist2 = newList1.Where(x => x.packetid == pNewList[j]).ToList();

                                    int ncount = newlist2.Count();
                                    Log4NetHelper.Debug($"{pNewList[j]},包内总数,ncount:{ncount}");
                                    for (int r = 1; r <= ncount; r++)
                                    {
                                        var row = newlist2[r - 1];
                                        if (row.layer == 0)
                                            row.layer = r;

                                        if (row.sortid == 0)
                                            row.sortid = r;

                                        newList.Add(row);
                                    }

                                }
                            }
                            catch (Exception ep)
                            {
                                var err = ep.Message;
                            }


                            string batch = null;
                            if (newList.Count() > 0)
                            {

                                //写入数据库
                                foreach (var row in newList)
                                {
                                    bus_wood_information wood_info = new bus_wood_information();
                                    bus_wood_task wood_task1 = new bus_wood_task();
                                    var _barcode = "";

                                    if (true)
                                    {
                                        try
                                        {
                                            //model信息写入 wood_info  wood_task1
                                            wood_info.id = Guid.NewGuid().ToString().ToUpper();
                                            _barcode = row.barcode;
                                            wood_info.barcode = row.barcode;

                                            wood_info.description_zh = row.description_zh;


                                            batch = batchName;
                                            wood_info.batch_id = batch;// row.orderid;

                                            wood_info.quantity = row.count;
                                            wood_info.raw_length = row.len;
                                            wood_info.raw_width = row.width;
                                            wood_info.finish_length = row.len;
                                            wood_info.finish_width = row.width;
                                            wood_info.finish_thickness = row.height;

                                            wood_info.order_number = !string.IsNullOrEmpty(row.orderid) ? row.orderid : "kongorder";
                                            wood_info.barcode1 = !string.IsNullOrEmpty(row.barcode1) ? row.barcode1 : "";
                                            wood_info.barcode2 = !string.IsNullOrEmpty(row.barcode2) ? row.barcode2 : "";

                                            wood_task1.barcode = _barcode;
                                            wood_task1.processing_flag = "1";

                                            wood_task1.program_m1 = !string.IsNullOrEmpty(row.barcode1) ? row.barcode1 : "";
                                            wood_task1.program_m2 = !string.IsNullOrEmpty(row.barcode2) ? row.barcode2 : "";
                                            wood_task1.feed_speed = 23;



                                            wood_info.barcode = _barcode;
                                            wood_task1.barcode = _barcode;

                                            wood_task1.feed_speed = 23;


                                            wood_info.import_mode = "1";
                                            wood_info.import_by = "mark";
                                            wood_info.office_id = "1";
                                            wood_info.material_code = string.IsNullOrEmpty(row.material_code) ? " " : row.material_code;
                                            wood_info.description_zh = " ";

                                            wood_task1.import_mode = "1";
                                            wood_task1.import_by = "mark";

                                            //创建bus_wood_task、bus_wood_information 的insert语句
                                            if (!string.IsNullOrEmpty(_barcode))
                                            {
                                                CreateSQLString("task", wood_task1.barcode, wood_task1.processing_flag, wood_task1.status, wood_task1.program_m1, wood_task1.program_m2, wood_task1.feed_speed, DateTime.Now, wood_task1.import_mode, wood_task1.import_by, DateTime.Now, wood_task1.send_to_cloud_flag, wood_task1.grooving_side1, wood_task1.grooving_side2);
                                                CreateSQLString("info", wood_info.id, wood_info.workshop_id, wood_info.barcode, wood_info.description_zh, wood_info.batch_id, wood_info.order_number, wood_info.production_date ?? null, wood_info.material_code, wood_info.quantity, wood_info.raw_length, wood_info.raw_width, wood_info.finish_length, wood_info.finish_width, wood_info.finish_thickness, wood_info.office_id, wood_info.line_code, wood_info.import_mode, wood_info.import_by, wood_info.dvm, wood_info.project_name, wood_info.barcode1, wood_info.barcode2);

                                                //wood_infoList.Add(wood_info);
                                                //wood_taskList.Add(wood_task1);
                                            }

                                        }
                                        catch (Exception ex)
                                        {

                                            errInfoDic[(int)index] = "[" + _barcode + "]  Err:" + ex.Message;
                                            errstr += errstr == null ? errInfoDic[(int)index] : "//" + errInfoDic[(int)index];
                                        }


                                        var nums = 0;
                                        var prevPackage = "";

                                        bus_package_list wood_list = new bus_package_list();

                                        var package_id = "";
                                        var barcode = "";
                                        var temp_barcode = "";
                                        try
                                        {
                                            temp_barcode = row.xiangid.ToString();
                                            package_id = row.packetid;
                                            barcode = row.barcode;  //.Replace("N", "").TrimStart().TrimEnd(); ;
                                            wood_list.batch_id = batchName;
                                            wood_list.package_id = package_id;
                                            wood_list.barcode = barcode;
                                            wood_list.x = 0;
                                            wood_list.y = 0;
                                            wood_list.z = 0;

                                            wood_list.sort = row.sortid;
                                            wood_list.layer = row.layer;

                                            try
                                            {
                                                wood_list.length = row.len;
                                            }
                                            catch
                                            {
                                                wood_list.length = 0;
                                            }
                                            try
                                            {
                                                wood_list.width = row.width;
                                            }
                                            catch
                                            {
                                                wood_list.width = 0;
                                            }
                                            try
                                            {
                                                wood_list.thickness = row.height;
                                            }
                                            catch
                                            {
                                                wood_list.thickness = 0;
                                            }

                                            wood_list.is_revolve = "0";

                                            wood_list.import_mode = "1";
                                            wood_list.import_by = "mark";

                                            wood_list.material_code = string.IsNullOrEmpty(row.material_code) ? " " : row.material_code;

                                            wood_list.description_zh = string.IsNullOrEmpty(row.description_zh) ? " " : row.description_zh;

                                            if (string.IsNullOrEmpty(prevPackage) || prevPackage == package_id)
                                            {
                                                nums += 1;
                                            }
                                            else
                                            {
                                                nums = 1;
                                            }

                                            //创建bus_package_list表的插入语句
                                            if (!string.IsNullOrEmpty(temp_barcode))
                                            {
                                                CreateSQLString2("list", wood_info.batch_id, wood_list.package_id, wood_list.barcode, wood_list.status, wood_list.sort, wood_list.layer, wood_list.x, wood_list.y, wood_list.z, DateTime.Now, wood_list.import_mode, wood_list.import_by, DateTime.Now, wood_list.send_to_cloud_flag, wood_list.is_revolve, wood_list.length, wood_list.width, wood_list.thickness, wood_list.material_code, wood_list.description_zh);
                                                //bus_Package_List_Models.Add(wood_list);
                                            }

                                        }
                                        catch (Exception ex)
                                        {

                                        }

                                        bus_packing_information pwood_info = new bus_packing_information();

                                        try
                                        {
                                            pwood_info.id = Guid.NewGuid().ToString().ToUpper();
                                            pwood_info.batch_id = batchName;// row.orderid;
                                            pwood_info.order_number = !string.IsNullOrEmpty(row.orderid) ? row.orderid : "kongorder";
                                            var sbarcode = row.xiangid;
                                            package_id = row.packetid;
                                            pwood_info.package_id = package_id;
                                            prevPackage = package_id;
                                            pwood_info.carton_length = 0;
                                            pwood_info.carton_width = 0;
                                            pwood_info.carton_height = 0;

                                            if (tempPackageDic == null || tempPackageDic.Keys.FirstOrDefault(x => x == package_id) == null)
                                            {
                                                tempPackageDic[package_id] = row.sortid;
                                            }
                                            else
                                            {
                                                if (tempPackageDic[package_id] < row.sortid)
                                                {
                                                    tempPackageDic[package_id] = row.sortid;
                                                }
                                            }

                                            pwood_info.quantity = tempPackageDic[package_id];
                                            pwood_info.layers = tempPackageDic[package_id];
                                            pwood_info.weight = 0;
                                            pwood_info.import_mode = "1";
                                            pwood_info.import_by = "mark";
                                            pwood_info.office_id = "1";
                                            pwood_info.description_zh = " ";
                                            //创建bus_packing_information表的插入语句
                                            if (!string.IsNullOrEmpty(barcode))
                                            {
                                                CreateSQLString2("info", pwood_info.id, pwood_info.workshop_id, pwood_info.batch_id, pwood_info.order_number, pwood_info.package_id, pwood_info.quantity, pwood_info.description_zh, pwood_info.office_id, pwood_info.import_mode, pwood_info.import_by, pwood_info.dvm, pwood_info.layers, pwood_info.carton_length, pwood_info.carton_width, pwood_info.carton_height, pwood_info.weight);
                                                //bus_Packing_Information_Models.Add(pwood_info);
                                            }

                                        }
                                        catch (Exception ex)
                                        {
                                        }

                                        lock (values)
                                        {
                                            index = index + 1;
                                            try
                                            {
                                                var tempV = index / rowsCount * 100;
                                                values = (int)Math.Floor(tempV);
                                            }
                                            catch (Exception ep)
                                            {
                                                var err = ep.Message;
                                            }
                                        }
                                    }
                                }
                                lock (values)
                                {
                                    values = 100;
                                }
                                Thread.Sleep(2000);

                                //执行事务写入数据库
                                using (MySqlConnection conn = new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["localDBConnectionString"].ToString()))
                                {
                                    conn.Open();
                                    MySqlCommand cmd = new MySqlCommand();
                                    cmd.Connection = conn;
                                    MySqlTransaction tx = conn.BeginTransaction();
                                    cmd.Transaction = tx;
                                    try
                                    {
                                        for (int n = 0; n < SQLStringList.Count; n++)
                                        {
                                            string strsql = SQLStringList[n].ToString();
                                            if (strsql.Trim().Length > 1)
                                            {
                                                cmd.CommandText = strsql;
                                                cmd.ExecuteNonQuery();
                                            }
                                            if ((n > 0 && n % 500 == 0) || n == SQLStringList.Count - 1)
                                            {
                                                tx.Commit();
                                                tx = conn.BeginTransaction();
                                            }
                                        }
                                    batch_task batch_Task = new batch_task();
                                    batch_Task.BatchName = batchName;
                                    batch_Task.ProductDate = DateTime.Now;
                                    batch_Task.Sequence = 1;
                                    batch_Task.ProductLine = "L1";
                                    batch_Task.PickingNo = batchName;
                                    batch_Task.Id = 1;
                                    //插入本地数据库
                                    localMysql.Insertable(batch_Task).ExecuteCommand();

                                        //tx.Commit();//原来一次性提交 
                                    }
                                    catch 
                                    {
                                        tx.Rollback();
                                    }
                                }

                                isSuspend = true;

                                if (errInfoDic.Count > 0)
                                {
                                }
                                else
                                {
                                    MessageBox.Show($"批量添加数据成功,{batchName}");
                                    Thread.Sleep(1000);
                                }

                            }
                            else
                            {
                                //MessageBox.Show(param.rm.GetString("NoData"), param.rm.GetString("OperationTips"), MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            }


                        }
                        catch (Exception ep)
                        {
                            Log4NetHelper.Error("InputDB---Error:" + ep.Message);
                        }
                    }
                    else
                    {
                    Log4NetHelper.Debug($"{batchName},已添加");
                    }

            }
            catch (Exception ex)
            {
                Log4NetHelper.Error($"获取批次数据异常:" + ex.StackTrace);

            }
        }
    }
}
